﻿CREATE PROCEDURE [acms].[Post_GetAllByMenuIdAndParentId]
	@MenuId int, 
	@ParentId int,
	@VersionTimeStamp datetime
AS
	declare @Ids table (PostId int, VersionId int)
	--insert all pending versions
	if @VersionTimeStamp is null begin
		insert into @Ids (PostId, VersionId)
		select Id, VersionId
		from [Post_View]
		where ParentId = @ParentId
			and VersionId in (Select PostVersionId from [PostMenu] where MenuId = @MenuId and Included = 1)
			and StartDate is null and EndDate is null
		--insert all that have a none expired version ie start date not null and end date null
		insert into @Ids (PostId, VersionId)
		select Id, VersionId
		from [Post_View]
		where ParentId = @ParentId
			and VersionId in (Select PostVersionId from [PostMenu] where MenuId = @MenuId and Included = 1)
			and StartDate is not null 
			and EndDate is null
			and Id not in (select PostId from @Ids)
		
		select a.* 
		from [Post_View] a 
			inner join @Ids b on a.VersionId = b.VersionId
		order by a.Sequence
	end
	else begin
		SELECT *
		FROM [Post_View]
		Where ParentId = @ParentId
			and VersionId in (Select PostVersionId from [PostMenu] where MenuId = @MenuId and Included = 1)
			and StartDate <= @VersionTimeStamp and isNull(EndDate, @VersionTimeStamp) >= @VersionTimeStamp
		ORDER By Sequence
	end
	
	
RETURN 0;